package com.je.app.persistence;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.persistence.Query;

import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.Session;

import com.je.app.model.Sesion;


public abstract class DaoGeneric {

	private Session sessionPersisted;

	public List list(String className){
		
    	Session session = HibernateUtil.getSession();
        session.beginTransaction();
        List list = session.createQuery("SELECT x FROM "+className+" x").list();
        session.getTransaction().commit();
        
        return list;
	}
	
	public void save(String className, Object object){
		
		Session session = HibernateUtil.getSession();
		session.beginTransaction();
        session.save(className, object);
        session.getTransaction().commit();
	}
	
	public Object get(Class classs, Integer id){

		Session session = HibernateUtil.getSession();
        session.beginTransaction();
        Object object = session.get(classs, (Serializable) id);
        session.getTransaction().commit();
        return object;
		
	}
	
	public Object getToUpdate(Class classs, Integer id){
		
		Session session = HibernateUtil.getSession();
        sessionPersisted.beginTransaction();
        Object object = sessionPersisted.get(classs, (Serializable) id);
        
        return object;
	}
	
	public void update(){
		
		sessionPersisted.getTransaction().commit();
		sessionPersisted = null;
	}
	
	public void persist(Object object){
		Session session = HibernateUtil.getSession();
        session.beginTransaction();
        session.saveOrUpdate(object);
        session.getTransaction().commit();
	}
	
	public void remove(Object object){
		Session session = HibernateUtil.getSession();
        session.beginTransaction();
        session.delete(object);
        session.getTransaction().commit();

	}
	
	public Object[][] obtenerResultadosMensuales(){
		
		Session session = HibernateUtil.getSession();
        session.beginTransaction();
        String query = "SELECT tabla1.anio, tabla1.mes, tabla1.suma_ingreso, tabla2.suma_egreso, tabla3.cantidad_turnos, tabla3.monto_consumicion, tabla3.monto_habitacion, suma_ingreso - suma_egreso + monto_consumicion + monto_habitacion as total"
        		+ " FROM (SELECT YEAR(m.fecha) as anio, MONTH(m.fecha) as mes, SUM(m.monto) as suma_ingreso FROM sistema_hotel.movimiento m  WHERE m.tipo_movimiento='Ingreso' GROUP BY mes, anio ORDER BY anio, mes DESC) AS tabla1,"
        		+ " (SELECT YEAR(m.fecha) as anio, MONTH(m.fecha) as mes, SUM(m.monto) as suma_egreso FROM sistema_hotel.movimiento m  WHERE m.tipo_movimiento='Egreso' GROUP BY mes, anio ORDER BY anio, mes DESC) AS tabla2,"
        		+ " (SELECT YEAR(t.hora_inicio) as anio, MONTH(t.hora_inicio) as mes , COUNT(t.id_turno) as cantidad_turnos, SUM(t.monto_consumicion) as monto_consumicion, SUM(t.monto_habitacion) as monto_habitacion FROM sistema_hotel.turno t GROUP BY mes, anio ORDER BY anio, mes DESC) AS tabla3 "
        		+ " WHERE tabla1.anio=tabla2.anio and tabla1.mes=tabla2.mes and tabla2.anio=tabla3.anio and tabla2.mes=tabla3.mes";
        SQLQuery consulta = session.createSQLQuery(query);
        consulta.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
        List listMap = consulta.list();
        session.getTransaction().commit();
        
        Object[][] lista = new Object[listMap.size()][8];
        int i = 0;
        String[] meses={"","Enero","Febrero","Marzo","Abril","Mayo","Junio","Julio",
        		"Agosto","Septiembre","Octubre","Noviembre","Diciembre"};
        for(Object object: listMap){
        	Map row = (Map) object;
        	Object [] mes = new Object[8];
        	mes[0]=meses[new Integer(""+row.get("mes"))];
        	mes[1]=row.get("anio");
        	mes[2]=row.get("cantidad_turnos");
        	mes[3]=row.get("monto_habitacion");
        	mes[4]=row.get("monto_consumicion");
        	mes[5]=row.get("suma_ingreso");
        	mes[6]=row.get("suma_egreso");
        	mes[7]=row.get("total");
        	lista[i]=mes;
        	i++;
        }
        
		return lista;
	}
}
